# @文件：spian.py
# @时间：2024/12/12 19:37
# @作者：Anthony
# @邮箱：不告诉你
from flask import Flask, render_template, jsonify, request
import pandas as pd
import pymysql

app = Flask(__name__)

# 数据库连接信息
host = 'localhost'
user = 'root'  # 替换为你的数据库用户名
password = 'root'  # 替换为你的数据库密码
database = 'flask'
port = 3306

# 从数据库读取数据的函数
def fetch_data_from_db():
    connection = pymysql.connect(host=host, user=user, password=password, database=database)
    query = "SELECT * FROM MuseumData"
    data = pd.read_sql(query, connection)
    connection.close()

    # 解析时间列
    data['时间'] = pd.to_datetime(data['visit_time'], errors='coerce')  # 注意与数据库字段名称匹配
    data['month'] = data['时间'].dt.month  # 提取月份
    data['hour'] = data['时间'].dt.hour  # 提取小时
    return data


# 主页面路由
@app.route('/')
def index():
    return render_template('index.html')


# 数据摘要 API
@app.route('/api/data_summary')
def data_summary():
    data = fetch_data_from_db()

    # 地区分布统计
    region_distribution = data['region'].value_counts().to_dict()

    # 热门博物馆统计
    top_museums = data['museum_name'].value_counts().head(10).to_dict()

    # 按小时访问分布
    hour_distribution = data['hour'].value_counts().sort_index().to_dict()

    # 按月访问分布
    monthly_distribution = data['month'].value_counts().sort_index().to_dict()

    # 数据摘要统计
    total_visits = len(data)  # 总访问量
    unique_users = data['user_name'].nunique()  # 独立用户数量
    unique_museums = data['museum_name'].nunique()  # 独特博物馆数量

    return jsonify({
        "region_distribution": region_distribution,
        "top_museums": top_museums,
        "hour_distribution": hour_distribution,
        "monthly_distribution": monthly_distribution,
        "summary_statistics": {
            "total_visits": total_visits,
            "unique_users": unique_users,
            "unique_museums": unique_museums
        }
    })


# 数据筛选 API
@app.route('/api/filter_data', methods=['GET'])
def filter_data():
    data = fetch_data_from_db()

    # 获取筛选参数
    region = request.args.get('region')  # 地区参数
    start_date = request.args.get('start_date')  # 开始日期
    end_date = request.args.get('end_date')  # 结束日期

    # 根据参数筛选数据
    filtered_data = data
    if region:
        filtered_data = filtered_data[filtered_data['region'] == region]
    if start_date:
        filtered_data = filtered_data[filtered_data['时间'] >= pd.to_datetime(start_date)]
    if end_date:
        filtered_data = filtered_data[filtered_data['时间'] <= pd.to_datetime(end_date)]

    # 筛选后的数据统计
    region_distribution = filtered_data['region'].value_counts().to_dict()
    top_museums = filtered_data['museum_name'].value_counts().head(10).to_dict()
    hour_distribution = filtered_data['hour'].value_counts().sort_index().to_dict()

    return jsonify({
        "region_distribution": region_distribution,
        "top_museums": top_museums,
        "hour_distribution": hour_distribution
    })


if __name__ == '__main__':
    app.run(debug=True)
